import { NextRequest, NextResponse } from "next/server";
import { SqliteDatabase } from "@/lib/sqlite-database";
import fs from 'fs';
import path from 'path';

export async function POST(request: NextRequest) {
  try {
    const database = new SqliteDatabase();

    // 清空所有位置信息参数数据
    const clearStmt = database['db'].prepare('DELETE FROM position_parameters');
    clearStmt.run();

    // 获取所有设备信息
    const allDevices = database['db'].prepare('SELECT * FROM devices').all();

    if (allDevices.length === 0) {
      return NextResponse.json({
        success: false,
        message: "数据库中没有设备数据，请先导入设备数据"
      });
    }

    // 读取Excel位置信息参数表CSV文件
    const csvFilePath = path.join(process.cwd(), 'excel', '06-21', '属性表-位置信息表.csv');
    if (!fs.existsSync(csvFilePath)) {
      return NextResponse.json({
        success: false,
        message: "Excel位置信息参数表文件不存在"
      });
    }

    const csvContent = fs.readFileSync(csvFilePath, 'utf-8');
    const lines = csvContent.split('\n').filter(line => line.trim() !== '');

    // 解析CSV数据，按表ID分组
    const parametersByTable = new Map<string, Array<{
      sequence: string;
      name: string;
      example: string;
      type: string;
      measurement: string;
      source: string;
    }>>();

    let currentTableId = '';

    for (const line of lines) {
      if (line.trim() === '') continue;
      const columns = line.split(',');
      if (columns.length >= 6) {
        const sequence = columns[0]?.trim() || '';
        const name = columns[1]?.trim() || '';
        const example = columns[2]?.trim() || '';
        const type = columns[3]?.trim() || '';
        const measurement = columns[4]?.trim() || '';
        const source = columns[5]?.trim() || '';

        // 检查是否是表标识行
        if (sequence.includes('表 C.')) {
          const tableMatch = sequence.match(/表 C\.\d+/);
          if (tableMatch) {
            currentTableId = tableMatch[0];
            if (!parametersByTable.has(currentTableId)) {
              parametersByTable.set(currentTableId, []);
            }
          }
          continue;
        }

        // 跳过标题行
        if (sequence === '序号' && name === '中文名称') {
          continue;
        }

        // 如果是有效的参数行，添加到当前表
        if (name && name !== '' && currentTableId && sequence.match(/^\d+$/)) {
          if (!parametersByTable.has(currentTableId)) {
            parametersByTable.set(currentTableId, []);
          }
          parametersByTable.get(currentTableId)!.push({
            sequence,
            name,
            example,
            type,
            measurement,
            source
          });
        }
      }
    }

    // 为每个设备添加对应的image.png参数
    let totalInserted = 0;
    for (const device of allDevices) {
      const positionTableId = device.position_table_id;
      if (!positionTableId) continue;

      let tableParams = parametersByTable.get(positionTableId);

      // 模糊匹配
      if (!tableParams) {
        for (const [excelTableId, params] of parametersByTable) {
          if (excelTableId.includes(positionTableId) || positionTableId.includes(excelTableId.replace('表 ', '').replace('表', ''))) {
            tableParams = params;
            break;
          }
        }
      }

      if (!tableParams) continue;

      for (const param of tableParams) {
        try {
          database.insertPositionParameter({
            device_id: device.id!,
            sequence: param.sequence, // 新增，导入序号
            parameter_name: param.name,
            parameter_value: "", // 可根据实际情况填写
            data_type: param.type,
            example_description: param.example === '—' ? '' : param.example,
            measurement_class: param.measurement === '—' ? '' : param.measurement,
            data_source: param.source,
            is_required: false
          });
          totalInserted++;
        } catch (error) {
          // 可选：记录错误
        }
      }
    }

    const finalCount = database['db'].prepare('SELECT COUNT(*) as total FROM position_parameters').get() as { total: number };

    return NextResponse.json({
      success: true,
      message: "位置信息参数数据重新导入成功",
      details: {
        actualInserted: totalInserted,
        databaseCount: finalCount.total
      }
    });
  } catch (error) {
    return NextResponse.json({
      success: false,
      message: "导入过程中发生错误",
      error: String(error)
    });
  }
}